﻿Imports DTO
Imports System.Data.OleDb

Public Class TheLoaiSachDAO
    Public Shared Function LayTheLoaiSach() As List(Of BangTheLoaiSachDTO)
        Dim danhsach As List(Of BangTheLoaiSachDTO) = New List(Of BangTheLoaiSachDTO)
        Dim sql = "select * From THELOAISACH"
        Dim connection As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = New OleDbCommand(Sql, connection)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        While reader.Read()
            Dim CTheLoai As New BangTheLoaiSachDTO()
            CTheLoai.MaTheLoai = reader.GetInt32(0)
            CTheLoai.TenTheLoai = reader.GetString(1)
            danhsach.Add(CTheLoai)
        End While
        connection.Close()
        Return danhsach
    End Function

    Public Shared Function LayMa(ByVal tenma As String) As Integer
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select MaTheLoai from THELOAISACH where TenTheLoai = '" + tenma + "'"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, cnn)
        Dim i As Integer = cmd.ExecuteScalar()
        cnn.Close()
        Return i
    End Function
    Public Shared Function LayTenTheLoai(ByVal Matheloai As Integer) As String
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select * from THELOAISACH where MaTheLoai=? "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, cnn)
        cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer)
        cmd.Parameters("@MaTheLoai").Value = Matheloai
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim chuoi As String = ""
        While reader.Read
            chuoi = reader.GetString(1)
        End While


        cnn.Close()
        Return chuoi
    End Function

    Public Shared Function LayBangTheLoai(ByVal tensach As String) As List(Of BangTheLoaiSachDTO)
        Dim BangTheLoai As List(Of BangTheLoaiSachDTO) = New List(Of BangTheLoaiSachDTO)
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select MaTheLoai,TenTheLoai from THELOAISACH where MaTheLoai in (select MaTheLoaiSach from SACH where TenSach = ?) "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, cnn)
        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters("@TenSach").Value = tensach
        Dim Reader As OleDbDataReader = cmd.ExecuteReader()
        While Reader.Read
            Dim TheLoai As BangTheLoaiSachDTO = New BangTheLoaiSachDTO()
            TheLoai.MaTheLoai = Reader.GetInt32(0)
            TheLoai.TenTheLoai = Reader.GetString(1)
            BangTheLoai.Add(TheLoai)
        End While
        
        cnn.Close()
        Return BangTheLoai
    End Function
End Class
